﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmnutri : Form
    {
        public frmnutri()
        {
            InitializeComponent();
        }
        private void frmnutri_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

            showType();
            autoid1();
        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
           
            sqlType = "select * from osm";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "osm");

            osm.DataSource = ds.Tables["osm"];
            osm.ValueMember = "OSMID";
            osm.DisplayMember = "OSMNAM";



        }
        private void autoid1()
        {
            DataTable max = new DataTable();
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string sql = "SELECT NUTID FROM nutri";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            odbcDA.Fill(max);

            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "nutri");
            SqlDataReader reader = cmd1.ExecuteReader();
            int max_id = 0;
            string max_data = "";

            if (max.Rows.Count != 0)
            {
                for (int i = 0; i < max.Rows.Count; i++)
                {
                    max_data = max.Rows[i]["NUTID"].ToString();
                    if (max_id < Convert.ToInt32(max_data))
                    {
                        max_id = Convert.ToInt32(max_data);
                    }
                }
                max_id++;
                max_data = Convert.ToString(max_id);
                if (max_data.Length == 1)
                {
                    max_data = "00000" + max_data;
                }
                else if (max_data.Length == 2)
                {
                    max_data = "0000" + max_data;
                }
                else if (max_data.Length == 3)
                {
                    max_data = "000" + max_data;
                }
                else if (max_data.Length == 4)
                {
                    max_data = "00" + max_data;
                }

                else if (max_data.Length == 5)
                {
                    max_data = "0" + max_data;
                }


                nutno.Text = max_data;
            }
            else
            {
                nutno.Text = "000001";
            }
        }
       
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            frmperson f5 = new frmperson();
            f5.ShowDialog();
        }

        private void button8_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string strdate1;
            DateTime datet;
            strdate1 = Convert.ToString(DateTime.Now.Year) + "-" + Convert.ToString(DateTime.Now.Month) + "-" + Convert.ToString(DateTime.Now.Day);
            datet = date.Value;
            if (nutno.Text != "" && hn.Text != "" && strdate1 != "" && age.Text != "" && wei.Text != "" && hei.Text != "" && desc.Text != "")
            {
                try
                {
                    string query = "insert into nutri values('" + nutno.Text + "','" + hn.Text + "','" + osm.SelectedValue + "','" + strdate1 + "','" + age.Text + "', '" + wei.Text + "','" + hei.Text + "','" + desc.Text + "')";
                    SqlCommand cmd = new SqlCommand(query, Conn);
                    cmd.ExecuteNonQuery();

                    string sql1 = "SELECT NUTID,HN,OSMNAM,DATE,AGE,WEIGHT,HEIGHT,DIST FROM nutri,osm WHERE nutri.OSMID=osm.OSMID ";
                    SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                    SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                    DataSet ds = new DataSet();


                    odbcDA.Fill(ds);

                    //MessageBox.Show("เพิ่มข้อมูลแล้ว");
                    this.dgvntri.DataSource = ds.Tables[0];
                    Format();

                }
                catch (Exception errToAdd)
                {
                    // showgrid();
                    MessageBox.Show("ข้อมูลผิดพลาดเนื่องจาก" + errToAdd.Message, "ผิดพลาด");
                }
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
            autoid1();

        }
        private void Format()
        {
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dgvntri.ColumnHeadersDefaultCellStyle = cs;
            this.dgvntri.Columns[0].HeaderText = "เลขที่ภาวะโภชนา";
            this.dgvntri.Columns[1].HeaderText = "เลขที่บัตร";
            this.dgvntri.Columns[2].HeaderText = "ผู้ตรวจ";
            this.dgvntri.Columns[3].HeaderText = "วันที่ตรวจ";
            this.dgvntri.Columns[4].HeaderText = "อายุ";
            this.dgvntri.Columns[5].HeaderText = "น้ำหนัก";
            this.dgvntri.Columns[6].HeaderText = "ส่วนสูง";
            this.dgvntri.Columns[7].HeaderText = "ผลการตรวจ";


            this.dgvntri.Columns[0].Width = 150;
            this.dgvntri.Columns[1].Width = 150;
            this.dgvntri.Columns[2].Width = 100;
            this.dgvntri.Columns[3].Width = 100;
            this.dgvntri.Columns[4].Width = 80;
            this.dgvntri.Columns[5].Width = 80;
            this.dgvntri.Columns[6].Width = 80;
            this.dgvntri.Columns[7].Width = 250;
    }

        private void button7_Click(object sender, EventArgs e)
        {

            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string query = "delete from nutri where  NUTID ='" + nutno.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            string sql = "SELECT NUTID,HN,OSMNAM,DATE,AGE,WEIGHT,HEIGHT,DIST FROM nutri where nurti.OSMID=osm.OSMID ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();


            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvntri.DataSource = ds.Tables[0];
            Format();
                   
        }
        private void showdata1()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql1 = "select * from person where PNNAM = '" + textBox1.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataReader reader = cmd1.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    hn.Text = reader["HN"].ToString();
                    idcard.Text = reader["IDCARD"].ToString();
                    name.Text = reader["PNNAM"].ToString();
                    bdate.Text = reader["BIRTH"].ToString();
                    add.Text = reader["ADDRESS"].ToString();


                }

                reader.Close();

            }


        }
        private void button1_Click(object sender, EventArgs e)
        {
           
           if (textBox1.Text == "") 
            {
              MessageBox.Show("กรุณากรอกรายชื่อต้องการค้นหา !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return; 
            }
            else
            {
                 showdata1();
            }
            
        }
        private void cleardata1()
        {
            nutno.Text = "";
            hn.Text = "";
            osm.SelectedValue = "";
            // date.value = "";
            age.Text = "";
            wei.Text = "";
            hei.Text = "";
            desc.Text = "";
        }
        private void button6_Click(object sender, EventArgs e)
        {
            cleardata1();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            frmperson f5 = new frmperson();
            f5.ShowDialog();
        }

        

      
    }
}
